package com.pkuse.bjousystem.repository.school;

import com.pkuse.bjousystem.dto.zhuanye.ZhuanYeDaiMaDTO;
import com.pkuse.bjousystem.entity.school.ZhuanYeDaiMa;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface ZhuanYeDaiMaRepository  extends CrudRepository<ZhuanYeDaiMa, String> {

    @Query("select new com.pkuse.bjousystem.dto.zhuanye.ZhuanYeDaiMaDTO(zydmb.zydm, zydmb.zymc) " +
            "from ZhuanYeDaiMa zydmb where zydmb.zydm in ( " +
            "select distinct xsb.zydm from XueShengBiYe xsbyb " +
            "inner join Student xsb on ((:xxdm is null or xsb.xxdm = :xxdm) and xsbyb.bynd is not null and xsbyb.byxq is not null and xsb.xh=xsbyb.xh))")
    List<ZhuanYeDaiMaDTO> listZhuanYeDaiMaForGraduation(@Param("xxdm") String xxdm);

    @Query("select new com.pkuse.bjousystem.dto.zhuanye.ZhuanYeDaiMaDTO(zydmb.zydm, zydmb.zymc) " +
            "from ZhuanYeDaiMa zydmb where zydmb.zydm in ( " +
            "select distinct xsb.zydm from XueShengXuanKeJieGuo xsxkjgb " +
            "inner join Student xsb on ((:xxdm is null or xsxkjgb.xxdm = :xxdm) and xsxkjgb.xh = xsb.xh and xsxkjgb.pzh is not null))")
    List<ZhuanYeDaiMaDTO> listZhuanYeDaiMaForRegistry(@Param("xxdm") String xxdm);

    @Query("select new com.pkuse.bjousystem.dto.zhuanye.ZhuanYeDaiMaDTO(zydmb.zydm, zydmb.zymc) " +
            "from ZhuanYeDaiMa zydmb where zydmb.zydm in ( " +
            "select distinct xsb.zydm from Student xsb where xsb.xjztdm='1' and (:xxdm is null or xsb.xxdm = :xxdm)" +
            ")")
    List<ZhuanYeDaiMaDTO> listZhuanYeDaiMaForEnrollment(@Param("xxdm") String xxdm);
}
